package dao;

import util.DBHelper;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

public class ProductDao {

    // 组合条件查询
    public  long selectProduct(String proname ,String price, String status, String tid){
        String sql = "select  p.*,tname,o.quantity from (select sum(quantity) as quantity,proid from orderdetail GROUP BY proid) as o right join  shopping_product p on o.proid = p.proid inner join shopping_type t  on p.tid = t.tid  ";
        List<String> params = new ArrayList<>();
        if (proname !=null && proname.trim().isEmpty() ==false){
            sql += " and proname like ?";
            params.add("%"+proname + "%");
        }

        if (price !=null && price.trim().isEmpty() == false){
            sql += " and price between ? and ?";
            final String[] split = price.split("~");
            params.add(split[0]);
            params.add(split[1]);
        }


        if (status !=null &&status.trim().isEmpty() ==false){
            sql += " and p.status = ?";
            params.add(status);
        }

        if (tid !=null && tid.trim().isEmpty() ==false){
            sql += " and p.tid = ?";
            params.add(tid);
        }

        return DBHelper.count(sql,params.toArray());
    }

    // 分页查询
    public List<Map<String,Object>> selectLimit(String proname ,String price, String status,int begin,int end,String tid){
//        String sql = "select * from shopping_product where 1=1";
        String sql = "select  p.*,tname,o.quantity from (select sum(quantity) as quantity,proid from orderdetail GROUP BY proid) as o right join  shopping_product p on o.proid = p.proid inner join shopping_type t  on p.tid = t.tid ";

        List<String> params = new ArrayList<>();
        if (proname !=null && proname.trim().isEmpty() ==false){
            sql += " and proname like ?";
            params.add("%"+proname + "%");
        }

        if (price !=null && price.trim().isEmpty() == false){
            sql += " and price between ? and ?";
            final String[] split = price.split("~");
            params.add(split[0]);
            params.add(split[1]);
        }

        if (status !=null && status.trim().isEmpty() ==false){
            sql += " and p.status = ?";
            params.add(status);
        }

        if (tid !=null && tid.trim().isEmpty() ==false){
            sql += " and p.tid = ?";
            params.add(tid);
        }

//        params.add(String.valueOf(begin));
//        params.add(String.valueOf(end));

        sql +=" limit "+begin + "," +end;

        return DBHelper.selectListMap(sql,params.toArray());
    }

    // 获取商品的tid
    public Map<String,Object> selectType(String proname){
        String sql = "select Tid from shopping_product where proname = ?";
        return DBHelper.selectOneMap(sql,proname);
    }

    // 根据商品类型名查询商品类型ID
    public Map<String,Object> selectTypeT(String tname){
        String sql = "select tid from shopping_type where tname = ? ";
        return DBHelper.selectOneMap(sql, tname);
    }

    // 商品 添加
    public int saveProduct(String name, String num, String tid,
                String price, String image, String message,String recommend){
        String sql = " insert into shopping_product values(default, ?, ?, ?, 1, ?, ?, ?, now(),?)";
        return DBHelper.update(sql, name, num, tid, price, image, message,recommend);
    }

    // 商品 修改
    public int modifyProduct(String id, String name, String num, String tid,
                             String price, String image, String message,String recommend){
        String sql = " update shopping_product set proname = ?, num = ?, tid = ?, price = ?, image = ?, message = ? ,recommend = ? where proid = ? ";
        return DBHelper.update(sql, name, num, tid, price, image, message, recommend,id);
    }

    // 商品 逻辑删除和复原
    public int removeStatusProduct(String id, String status){
        String sql = " update shopping_product set status = ? where proid = ? ";
        return DBHelper.update(sql, status, id);
    }

    // 商品 物理删除
    public int removeProduct(String id){
        String sql = " delete from shopping_product where proid = ? ";
        return DBHelper.update(sql, id);
    }

    //根据商品名称获取商品id
    public Map<String,Object> getProid(String proname){
        String sql = "select proid from shopping_product where proname = ?";
        return DBHelper.selectOneMap(sql,proname);
    }
//    //获取商品销量
//    public Map<String, Object> getTotal(String proid){
//        String sql = "select sum(quantity) as total where proid = ?";
//        return DBHelper.selectOneMap(sql,proid);
//    }
    //商品推荐修改
    public int modifyRecommend(String proid, String recommend){
        String sql = " update shopping_product set recommend = ? where proid = ? ";
        return DBHelper.update(sql, recommend, proid);
    }

    //根据商品id查询单个商品数据
    public Map<String, Object> getMessage(String proid){
        String sql = "select  * from shopping_product  where proid = ?";
        return DBHelper.selectOneMap(sql,proid);
    }

    //通过商品详情查询购买的商品

    public List<Map<String, Object>> getBuyProduct(String proid){
        String sql = "select  proid,proname,tid,price,image,message,recommend from shopping_product  where proid = ?";
        return DBHelper.selectListMap(sql,proid);
    }
}
